package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.IGatewayTransManagerDao;
import com.sq.partner.manager.mybatis.model.GatewayTrans;
import com.sq.partner.manager.mybatis.model.GatewayTxLog;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class GatewayTransManagerDaoImpl extends BaseDaoImpl implements IGatewayTransManagerDao {
	private static Logger logger = LogUtil.getLog(GatewayTransManagerDaoImpl.class);


	@Override
	public Page<GatewayTrans> queryPage(Page<GatewayTrans> page, String channel, String trxDate,
			String trxCode, String deptCode, String mark,String subPartnerNo,String applicationCode) throws SQLException {
		Connection conn = null;
		// 查总条数
		PreparedStatement pstmt = null;
		// 查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			StringBuffer sqlbuf = new StringBuffer();
			//sqlbuf.append("select l.msg_id,i.channel_name,t.trans_name,to_char(l.start_time, 'yyyy-MM-dd HH24:MI:SS') as operate_Date,trim(to_char(l.total_elapsed/1000,'9999990.999')) as total_elapsed,trim(to_char(l.out_elapsed/1000,'9999990.999'))as out_elapsed,nvl(l.error_code,'0000') error_code,nvl2(l.error_code,l.error_desc,'交易成功') error_desc from t_gateway_tx_log l,t_gateway_channel_transtype t,t_gateway_channel_info i, T_GATEWAY_CHANNEL_APP H where l.channel_code = t.fk_channel_code(+) and l.tx_code = t.trans_code(+) and l.channel_code = i.channel_code(+) and l.channel_code = h.channel_code(+) and l.app_id = h.app_id(+) ");
			//为提高性能 现拆分三种情况
			//第一种总公司 ； 分公司 ； 支公司 及以下
			if("00".equalsIgnoreCase(deptCode)){
				sqlbuf.append("SELECT * FROM T_GATEWAY_CONVERT_STATUS G RIGHT JOIN (SELECT L.MSG_ID,TEM_DEPT.CHANNEL_NAME,T.TRANS_NAME,TO_CHAR(L.START_TIME, 'yyyy-MM-dd HH24:MI:SS') AS OPERATE_DATE,"
						+ " TRIM(TO_CHAR(L.TOTAL_ELAPSED / 1000, '9999990.999')) AS TOTAL_ELAPSED,TRIM(TO_CHAR(L.OUT_ELAPSED / 1000, '9999990.999')) AS OUT_ELAPSED,NVL(L.ERROR_CODE, '0000') ERROR_CODE,"
						+ " NVL2(L.ERROR_CODE, L.ERROR_DESC, '交易成功') ERROR_DESC FROM T_GATEWAY_TX_LOG L  left JOIN t_gateway_app e on  L.APP_ID = e.appid INNER JOIN T_GATEWAY_CHANNEL_APP H"
						+ " ON L.CHANNEL_CODE = H.CHANNEL_CODE AND L.APP_ID = H.APP_ID INNER JOIN T_GATEWAY_CHANNEL_TRANSTYPE T"
						+ " ON L.CHANNEL_CODE = T.FK_CHANNEL_CODE AND L.APP_ID = T.APP_ID AND L.TX_CODE = T.TRANS_CODE INNER JOIN T_GATEWAY_CHANNEL_INFO TEM_DEPT ON L.CHANNEL_CODE = TEM_DEPT.CHANNEL_CODE"
						);
			}else if(2 == deptCode.length()){
				sqlbuf.append("SELECT * FROM T_GATEWAY_CONVERT_STATUS G RIGHT JOIN (SELECT L.MSG_ID,TEM_DEPT.CHANNEL_NAME,T.TRANS_NAME,TO_CHAR(L.START_TIME, 'yyyy-MM-dd HH24:MI:SS') AS OPERATE_DATE,"
						+ " TRIM(TO_CHAR(L.TOTAL_ELAPSED / 1000, '9999990.999')) AS TOTAL_ELAPSED,TRIM(TO_CHAR(L.OUT_ELAPSED / 1000, '9999990.999')) AS OUT_ELAPSED,NVL(L.ERROR_CODE, '0000') ERROR_CODE,"
						+ " NVL2(L.ERROR_CODE, L.ERROR_DESC, '交易成功') ERROR_DESC FROM T_GATEWAY_TX_LOG L  left JOIN t_gateway_app e"
						+ " on L.APP_ID = e.appid  "
						+ "INNER JOIN T_GATEWAY_CHANNEL_APP H"
						+ " ON L.CHANNEL_CODE = H.CHANNEL_CODE AND L.APP_ID = H.APP_ID INNER JOIN T_GATEWAY_CHANNEL_TRANSTYPE T"
						+ " ON L.CHANNEL_CODE = T.FK_CHANNEL_CODE AND L.APP_ID = T.APP_ID AND L.TX_CODE = T.TRANS_CODE INNER JOIN T_GATEWAY_CHANNEL_INFO TEM_DEPT ON L.CHANNEL_CODE = TEM_DEPT.CHANNEL_CODE AND TEM_DEPT.DEPT_CODE = '"+deptCode+"'"
						);
			}else{
				sqlbuf.append("SELECT * FROM T_GATEWAY_CONVERT_STATUS G RIGHT JOIN (SELECT L.MSG_ID,TEM_DEPT.CHANNEL_NAME,T.TRANS_NAME,TO_CHAR(L.START_TIME, 'yyyy-MM-dd HH24:MI:SS') AS OPERATE_DATE,"
						+ "TRIM(TO_CHAR(L.TOTAL_ELAPSED / 1000, '9999990.999')) AS TOTAL_ELAPSED,TRIM(TO_CHAR(L.OUT_ELAPSED / 1000, '9999990.999')) AS OUT_ELAPSED,"
						+ " NVL(L.ERROR_CODE, '0000') ERROR_CODE,NVL2(L.ERROR_CODE, L.ERROR_DESC, '交易成功') ERROR_DESC FROM T_GATEWAY_TX_LOG L left JOIN t_gateway_app e"
						+ " on L.APP_ID = e.appid"
						+ " INNER JOIN (SELECT T9.CHANNEL_CODE, T9.CHANNEL_NAME FROM T_GATEWAY_CHANNEL_INFO T9"
						+ " INNER JOIN (SELECT FA1.PARTNER_CODE  FROM T_NOCAR_PLAN FA1 INNER JOIN (SELECT M.DEPT_CODE"
						+ " FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH DEPT_CODE = ('"+deptCode+"') CONNECT BY NOCYCLE"
						+ "   PRIOR M.DEPT_CODE = M.SNR_DEPT) TBL_DEPT_NOCAR ON FA1.DEPT_CODE = TBL_DEPT_NOCAR.DEPT_CODE UNION"
						+ " SELECT FA2.PARTNER_NO FROM T_TRAD_INSU_PLAN FA2 INNER JOIN (SELECT M.DEPT_CODE"
						+ " FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH DEPT_CODE = ('"+deptCode+"') CONNECT BY NOCYCLE "
						+ " PRIOR  M.DEPT_CODE = M.SNR_DEPT) TBL_DEPT_PRE_LIFE ON FA2.DPT_CDE = TBL_DEPT_PRE_LIFE.DEPT_CODE"
						+ " UNION SELECT FA3.PARTNER_NO FROM T_NLIFE_PLAN FA3 INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M"
						+ " WHERE LENGTH(M.DEPT_CODE) = 8 START WITH DEPT_CODE = ('"+deptCode+"') CONNECT BY NOCYCLE PRIOR"
						+ " M.DEPT_CODE = M.SNR_DEPT) TBL_DEPT_NLIFE ON FA3.DPT_CDE = TBL_DEPT_NLIFE.DEPT_CODE) TBL_TEM_CHANNEL"
						+ " ON T9.CHANNEL_CODE = TBL_TEM_CHANNEL.PARTNER_CODE) TEM_DEPT ON L.CHANNEL_CODE = TEM_DEPT.CHANNEL_CODE"
						+ " INNER JOIN T_GATEWAY_CHANNEL_APP H ON L.CHANNEL_CODE = H.CHANNEL_CODE AND L.APP_ID = H.APP_ID"
						+ " INNER JOIN T_GATEWAY_CHANNEL_TRANSTYPE T ON L.CHANNEL_CODE = T.FK_CHANNEL_CODE AND L.APP_ID = T.APP_ID"
						+ " AND L.TX_CODE = T.TRANS_CODE");
			}
			
			String sql;
			
			List<String> list = new ArrayList<String>();		
			if (StrUtil.isNotEmpty(channel)) {
				sqlbuf.append(" and l.channel_code= ?");
				list.add(channel);
			}
			if (StrUtil.isNotEmpty(trxCode)) {
				sqlbuf.append(" and l.tx_Code = ?");
				list.add(trxCode);
			}
			if (StrUtil.isNotEmpty(subPartnerNo)) {
				sqlbuf.append(" and l.tx_Code = ?");
				list.add(subPartnerNo);
			}
			if (StrUtil.isNotEmpty(applicationCode)) {
				sqlbuf.append(" and  e.appname=?");
				list.add(applicationCode);
			}
			if (StrUtil.isNotEmpty(trxDate)) {
				sqlbuf.append(" and l.start_time >= to_date( ? ,'yyyy-MM-dd')");
				sqlbuf.append(" and l.start_time < to_date( ? ,'yyyy-MM-dd')+1");
				list.add(trxDate);
				list.add(trxDate);
			}
			/*List<DeptDto> depts = page.getDepts();

			String deptStr = "";
			if (null != depts && depts.size() > 0) {
				StringBuffer deptStrBuf = new StringBuffer();
				for (int i = 0; i < depts.size(); i++) {
					deptStrBuf.append("'" + depts.get(i).getDeptCode() + "',");
				}
				deptStr = deptStrBuf.toString();
				deptStr = deptStr.substring(0, deptStr.length() - 1);
			}

			sqlbuf.append(" and i.dept_code in (");
			if (StrUtil.isNotEmpty(deptStr)) {
				// sqlbuf.append("select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ("+deptstr+")");
				sqlbuf.append("select d.dept_code from t_core_dept d where length(d.dept_code)=2 and d.snr_dept in ("
						+ deptStr + ") or d.dept_code in (" + deptStr + ")");
			} else {
				sqlbuf.append("''");
			}
			sqlbuf.append(" )");*/
			sqlbuf.append(") F ON F.ERROR_CODE = G.GATEWAY_ERRORCODE");
			if (StrUtil.isNotEmpty(mark)) {
				sqlbuf.append(" where G.NORMAL_STATE = '" + mark+"'");
			}
			sqlbuf.append(" order by F.OPERATE_DATE desc");
			sql = sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from (" + sql + ")");
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i + 1, list.get(i));
			}
			rs = pstmt.executeQuery();
			// 由于此逻辑关联基表较多所以采用 分析函数 算总页数减少数据库连接次数和全表扫描
			Long total=0l;
			if(rs.next()){
				total = rs.getLong(1);
				logger.debug("总记录数++++++"+total);
			}
			// 查出分页
			page.setTotal(total);
			SqlUtil sqlUtil = new SqlUtil();
			String pagesql = sqlUtil.buildPageSqlForOracle(sql, page).toString();
			logger.debug("执行SQL：" + pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				pstmt1.setObject(i + 1, list.get(i));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		} finally {
			this.close(rs);
			this.close(pstmt1);
			this.close(pstmt);
			this.close(conn);
		}
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<GatewayTxLog> getDetailTxLogVo(GatewayTxLog gatewayTxLog) throws SQLException {
		// TODO Auto-generated method stub
		return queryList("getDetialGatewayTxLog", gatewayTxLog);
	}

}
